package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.AccountOverallDao;
import csc.fresher.entity.AccountState;
import csc.fresher.entity.AccountTransactionState;
import csc.fresher.entity.CustomerAccountOverall;

@Repository
public class AccountOverallDaoImpl implements AccountOverallDao {

	@Autowired
	DataSource dataSource;
	private static final Logger logger = Logger
			.getLogger(SavingAccountDaoImpl.class);

	// for test
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://20.203.182.23:3306/finalProject", "root",
					"root");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// end test

	@Override
	public List<CustomerAccountOverall> getAccountByState(String accountState,
			String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`"
				+ ") u WHERE `AccountState`=? and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, accountState);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccountByIDCardNumber(
			long idCardNumber, String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`"
				+ ") u WHERE `IDCardNumber`=? and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, idCardNumber);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccountByFirstName(String firstName,
			String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`" + ") u WHERE `FirstName` LIKE '%"
				+ firstName + "%' and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccoungByPhoneNumber(
			String phoneNumber, String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`" + ") u WHERE `PhoneNumber` LIKE '%"
				+ phoneNumber + "%' and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccountByAddress(String address,
			String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`" + ") u WHERE `Address` LIKE '%"
				+ address + "%' and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccountByAccountNumber(
			long accountNumber, String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`" + ") u WHERE `" + accountType
				+ "Number`=? and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, accountNumber);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAccountByEmail(String email,
			String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`" + ") u WHERE `Email` LIKE '%" + email
				+ "%' and `isRemovable`=0";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	@Override
	public List<CustomerAccountOverall> getAllAccountByIDCardNumber(
			long idCardNumber) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM `customer` as `c` "
				+ "inner join (select * from `depositAccount`) as `d` "
				+ "inner join (select * from `savingaccount`) as `s` "
				+ "Where `IDCardNumber`=? Group By `IDCardNumber` and `c`.`isRemovable`=0";

		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, idCardNumber);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();			
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	// For test
	public static void main(String[] agrs) {
		AccountOverallDao depAccountDao = new AccountOverallDaoImpl();
		List<CustomerAccountOverall> resultList = depAccountDao
				.getAccoungByPhoneNumber("015", "SavingAccount");

		for (int i = 0; i < resultList.size(); i++) {
			CustomerAccountOverall depAccount = (CustomerAccountOverall) resultList
					.get(i);
			System.out.println(depAccount.getAccountNumber());
		}
	}
	// end test

	@Override
	public List<CustomerAccountOverall> getActiveAccountByIDCardNumber(
			long idCardNumber, String accountType) {
		List<CustomerAccountOverall> resultList = new ArrayList<CustomerAccountOverall>();
		String query = "SELECT * FROM (SELECT * FROM `" + accountType
				+ "`,`Customer` " + "WHERE `Customer`.`CustomerID`=`"
				+ accountType + "`.`Customer_CustomerID` GROUP BY `"
				+ accountType + "Number`"
				+ ") u WHERE `IDCardNumber`=? and `isRemovable`=0 and `AccountState`='active'";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setLong(1, idCardNumber);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				CustomerAccountOverall customerAccount = new CustomerAccountOverall();
				customerAccount.setAccountNumber(rs.getLong(accountType
						+ "Number"));
				customerAccount.setAccountType(rs.getByte("AccountType"));
				customerAccount.setAccountBalance(rs.getDouble(accountType
						+ "Balance"));
				customerAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				customerAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				customerAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				customerAccount
						.setAccountTransactionState(AccountTransactionState
								.valueOf(rs
										.getString("AccountTransactionState")
										.toUpperCase()));
				customerAccount.setCustomerID(rs.getLong("CustomerID"));
				customerAccount.setCustomer_CustomerID(rs
						.getLong("Customer_CustomerID"));
				customerAccount.setRemovable(rs.getBoolean("isRemovable"));
				customerAccount.setIdCardNumber(rs.getLong("IDCardNumber"));
				customerAccount.setFirstName(rs.getString("FirstName"));
				customerAccount.setLastName(rs.getString("LastName"));
				customerAccount.setMidName(rs.getString("MidName"));
				customerAccount.setAddress(rs.getString("Address"));
				customerAccount.setAddress2(rs.getString("Address_2"));
				customerAccount.setPhoneNumber(rs.getString("PhoneNumber"));
				customerAccount.setPhoneNumber2(rs.getString("PhoneNumber_2"));
				customerAccount.setEmail(rs.getString("Email"));
				customerAccount.setEmail2(rs.getString("Email_2"));
				resultList.add(customerAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}
}
